/**
 * Description:
 * Class Name:PreparedStatementTest
 * Date:2022/4/4 20:28
 *
 * @author LTisme
 * @version JAVA17
 */
package 疯狂Java讲义.第13章_MySQL数据库和JDBC编程._13_4_执行SQL语句的方式._13_4_3_使用PreparedStatement执行SQL语句;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Properties;

public class PreparedStatementTest {
    private String driver;
    private String url;
    private String user;
    private String pwd;

    public void initParam(String paramFile) throws Exception {
        var props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pwd = props.getProperty("pwd");
    }

    public void insertUseStatement(Connection connection) throws Exception{
        long start = System.currentTimeMillis();        // 记录初始时间
        try (
                Statement statement = connection.createStatement()
                ){
            for (int i = 0; i < 100; i++) {
                statement.executeUpdate("insert into student_table values(" +
                        "null, 'name_" + i + "', 1);");
            }
            System.out.println("使用Statement对象执行100次插入语句所耗时间（毫秒）是：" + (System.currentTimeMillis() - start));
        }
    }

    public void insertUsePreparedStatement(Connection connection, String sql) throws Exception{
        long start = System.currentTimeMillis();        // 记录初始时间
        try (
                PreparedStatement preparedStatement = connection.prepareStatement(sql)
                ){
            for (int i = 0; i < 100; i++) {
                preparedStatement.setString(1, "name=" + i);
                preparedStatement.executeUpdate();
            }
            System.out.println("使用PreparedStatement对象执行100次插入语句所耗时间（毫秒）是：" + (System.currentTimeMillis() - start));
        }
    }

    public static void main(String[] args) throws Exception{
        var pst = new PreparedStatementTest();
        pst.initParam("./Chapter_13_mysql.ini");
        Class.forName(pst.driver);
        Connection connection = DriverManager.getConnection(pst.url, pst.user, pst.pwd);

        String sql_for_prepareStatement = "insert into student_table " +
                "values(null, ?, 1);";

        pst.insertUseStatement(connection);
        pst.insertUsePreparedStatement(connection, sql_for_prepareStatement);
    }
}
//使用Statement对象执行100次插入语句所耗时间（毫秒）是：685
//使用PreparedStatement对象执行100次插入语句所耗时间（毫秒）是：681